
DROP TABLE Seguridad
go



DROP TABLE Aviso_Postulantes
go



DROP TABLE Aviso
go



DROP TABLE Ofertante
go



DROP TABLE Postulante
go



DROP TYPE hierarchyid
go



DROP TYPE geometry
go



DROP TYPE geography
go



CREATE TYPE geography
	EXTERNAL NAME Microsoft.SqlServer.Types.[Microsoft.SqlServer.Types.SqlGeography]
go



CREATE TABLE Aviso
( 
	n_idaviso            int IDENTITY ( 1,1 ) ,
	n_idofertante        int  NULL ,
	c_titulo             varchar(70) COLLATE Modern_Spanish_CI_AS  NOT NULL ,
	d_fecinicio          datetime  NOT NULL ,
	d_fectermino         datetime  NOT NULL ,
	c_descripcion_puesto varchar(3000) COLLATE Modern_Spanish_CI_AS  NOT NULL 
)
go



ALTER TABLE Aviso
	ADD CONSTRAINT XPKAviso PRIMARY KEY  CLUSTERED (n_idaviso ASC)
go



CREATE TABLE Aviso_Postulantes
( 
	n_postular           int IDENTITY ( 1,1 ) ,
	n_idaviso            int  NOT NULL ,
	n_idpostulante       int  NULL ,
	d_fecinscripcion     datetime  NOT NULL ,
	c_caracteristicas    varchar(3000) COLLATE Modern_Spanish_CI_AS  NOT NULL ,
	b_ganador            bit  NOT NULL 
)
go



ALTER TABLE Aviso_Postulantes
	ADD CONSTRAINT XPKAviso_Postulantes PRIMARY KEY  CLUSTERED (n_postular ASC)
go



CREATE TABLE Ofertante
( 
	n_idofertante        int IDENTITY ( 1,1 ) ,
	c_nroruc             char(11) COLLATE Modern_Spanish_CI_AS  NOT NULL ,
	c_razon_social       varchar(70) COLLATE Modern_Spanish_CI_AS  NOT NULL ,
	c_correo             varchar(50) COLLATE Modern_Spanish_CI_AS  NOT NULL ,
	c_contrasena         varchar(20) COLLATE Modern_Spanish_CI_AS  NOT NULL 
)
go



ALTER TABLE Ofertante
	ADD CONSTRAINT XPKOfertante PRIMARY KEY  CLUSTERED (n_idofertante ASC)
go



CREATE TABLE Postulante
( 
	n_idpostulante       int IDENTITY ( 1,1 ) ,
	c_ape_paterno        varchar(30) COLLATE Modern_Spanish_CI_AS  NOT NULL ,
	c_ape_materno        varchar(30) COLLATE Modern_Spanish_CI_AS  NOT NULL ,
	c_nombres            varchar(30) COLLATE Modern_Spanish_CI_AS  NOT NULL ,
	c_sexo               char(1) COLLATE Modern_Spanish_CI_AS  NOT NULL ,
	d_fecnacimiento      datetime  NOT NULL ,
	c_correo             varchar(50) COLLATE Modern_Spanish_CI_AS  NULL ,
	c_contrasena         varchar(20) COLLATE Modern_Spanish_CI_AS  NULL ,
	c_curriculum         varchar(3000) COLLATE Modern_Spanish_CI_AS  NULL 
)
go



ALTER TABLE Postulante
	ADD CONSTRAINT XPKPostulante PRIMARY KEY  CLUSTERED (n_idpostulante ASC)
go



CREATE TABLE Seguridad
( 
	id_codseg            int  NOT NULL ,
	t_usuario            varchar(50) COLLATE Modern_Spanish_CI_AS  NOT NULL ,
	correo               varchar(50) COLLATE Modern_Spanish_CI_AS  NOT NULL ,
	contrasena           varchar(50) COLLATE Modern_Spanish_CI_AS  NOT NULL 
)
go



ALTER TABLE Seguridad
	ADD CONSTRAINT XPKSeguridad PRIMARY KEY  CLUSTERED (id_codseg ASC)
go




ALTER TABLE Aviso
	ADD CONSTRAINT R_3 FOREIGN KEY (n_idofertante) REFERENCES Ofertante(n_idofertante)
go




ALTER TABLE Aviso_Postulantes
	ADD CONSTRAINT R_1 FOREIGN KEY (n_idpostulante) REFERENCES Postulante(n_idpostulante)
go




ALTER TABLE Aviso_Postulantes
	ADD CONSTRAINT R_2 FOREIGN KEY (n_idaviso) REFERENCES Aviso(n_idaviso)
go



IF EXISTS 
     (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('pr_AvisosPostulantes_Listar')) 
DROP PROCEDURE pr_AvisosPostulantes_Listar
go




CREATE PROCEDURE pr_AvisosPostulantes_Listar   
   
 AS SELECT     dbo.Aviso_Postulantes.n_postular, dbo.Aviso.c_titulo, dbo.Postulante.c_ape_paterno, dbo.Postulante.c_ape_materno, dbo.Postulante.c_nombres, 
                      dbo.Aviso.d_fecinicio, dbo.Aviso.d_fectermino, dbo.Aviso_Postulantes.b_ganador
FROM         dbo.Aviso_Postulantes LEFT OUTER JOIN
                      dbo.Aviso ON dbo.Aviso_Postulantes.n_idaviso = dbo.Aviso.n_idaviso LEFT OUTER JOIN
                      dbo.Postulante ON dbo.Aviso_Postulantes.n_idpostulante = dbo.Postulante.n_idpostulante
go



IF EXISTS 
     (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('pr_Avisos_Listar')) 
DROP PROCEDURE pr_Avisos_Listar
go




CREATE PROCEDURE pr_Avisos_Listar   
   
 AS SELECT     dbo.Aviso.n_idaviso, dbo.Ofertante.c_razon_social, dbo.Aviso.c_titulo, dbo.Aviso.d_fecinicio, dbo.Aviso.d_fectermino, 
                      dbo.Aviso.c_descripcion_puesto
FROM         dbo.Aviso LEFT OUTER JOIN
                      dbo.Ofertante ON dbo.Aviso.n_idofertante = dbo.Ofertante.n_idofertante
go



IF EXISTS 
     (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('pw_AvisosPostulantes_Ganador')) 
DROP PROCEDURE pw_AvisosPostulantes_Ganador
go




CREATE PROCEDURE pw_AvisosPostulantes_Ganador @n_postular int , @b_ganador varchar(1)   
   
 AS UPDATE [Aviso_Postulantes] 

SET  [b_ganador]	 = @b_ganador

WHERE 
	( [n_postular]	 = @n_postular)
go



IF EXISTS 
     (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('pw_AvisosPostulantes_Registrar')) 
DROP PROCEDURE pw_AvisosPostulantes_Registrar
go




CREATE PROCEDURE pw_AvisosPostulantes_Registrar @n_idaviso int , @n_idpostulante int , @d_fecinscripcion datetime , @c_caracteristicas varchar(3000) , @b_ganador varchar(1)   
   
 AS INSERT INTO [BDBolsa].[dbo].[Aviso_Postulantes] 
	 ([n_idaviso],
	 [n_idpostulante],
	 [d_fecinscripcion],
	 [c_caracteristicas],
	 [b_ganador]) 
 
VALUES 
	(@n_idaviso,
	 @n_idpostulante,
	 @d_fecinscripcion,
	 @c_caracteristicas,
	 @b_ganador)
go



IF EXISTS 
     (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('pw_Avisos_Registrar')) 
DROP PROCEDURE pw_Avisos_Registrar
go




CREATE PROCEDURE pw_Avisos_Registrar @n_idofertante int , @c_titulo varchar(70) , @d_fecinicio datetime , @d_fectermino datetime , @c_descripcion_puesto varchar(3000) , @xIdentity int  OUTPUT   
   
 AS INSERT INTO [Aviso] 
	 ( [n_idofertante],
	 [c_titulo],
	 [d_fecinicio],
	 [d_fectermino],
	 [c_descripcion_puesto]) 
 
VALUES 
	(@n_idofertante,
	 @c_titulo,
	 @d_fecinicio,
	 @d_fectermino,
	 @c_descripcion_puesto)

SET @xIdentity = SCOPE_IDENTITY()
go



IF EXISTS 
     (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('pw_Ofertante_Registrar')) 
DROP PROCEDURE pw_Ofertante_Registrar
go




CREATE PROCEDURE pw_Ofertante_Registrar @c_nroruc char(11) , @c_razon_social varchar(70) , @c_correo varchar(20) , @c_contrasena varchar(20) , @xIdentity int  OUTPUT   
   
 AS INSERT INTO [Ofertante] 
	 ([c_nroruc],
	 [c_razon_social],
	 [c_correo],
	 [c_contrasena]) 
 
VALUES 
	(@c_nroruc,
	 @c_razon_social,
	 @c_correo,
	 @c_contrasena)

SET @xIdentity = SCOPE_IDENTITY()
go



IF EXISTS 
     (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('pw_Postulante_Registrar')) 
DROP PROCEDURE pw_Postulante_Registrar
go




CREATE PROCEDURE pw_Postulante_Registrar @c_ape_paterno varchar(30) , @c_ape_materno varchar(30) , @c_nombres varchar(30) , @c_sexo char(1) , @d_fecnacimiento datetime , @c_correo varchar(20) , @c_contrasena varchar(20) , @xIdentity int  OUTPUT   
   
 AS INSERT INTO [Postulante] 
	 ([c_ape_paterno],
	 [c_ape_materno],
	 [c_nombres],
	 [c_sexo],
	 [d_fecnacimiento],
	 [c_correo],
	 [c_contrasena]) 
 
VALUES 
	(@c_ape_paterno,
	 @c_ape_materno,
	 @c_nombres,
	 @c_sexo,
	 @d_fecnacimiento,
	 @c_correo,
	 @c_contrasena)


SET @xIdentity = SCOPE_IDENTITY()
go


